<?php 
	jimport('joomla.application.component.model');
	class kinh_doanhModelorther_sp extends JModel
	{
        //lay danh sach san pham  theo id bill
        function ds_sp_bill($id)
        {
            $db = &JFactory::getDBO();
            $sql = "SELECT pr.name as name_pro, pr.id as id_pro, ioe.number as number, ioe.id as id_ioe
                    FROM #__erp_warehouse_bill_io_extend ioe, #__erp_product pr
                    WHERE ioe.id_product = pr.id
                    AND ioe.id_bill = '".$id."' ";
                    
            $db->setQuery($sql);
            //print_r($db);die;
            return $db->loadObjectList();           
        }
            
	    //su luoc order hang tu kho
        function su_luoc_dat_hang_tu_kho($date=null)
        {
            if($date == null)
            {
                $date = date('Y-m-d');
            }
            //dinh dang lai $date giong voi CSDL
            else
            {
                $d = substr($date,3,2);
                $m = substr($date,0,2);
                $y = substr($date,6,4);
                $date = $y."-".$m."-".$d;
                
            }  
            //echo $date;die;
            $db = &JFactory::getDBO();
            $sql = "SELECT * 
                    FROM #__erp_warehouse_bill_io
                    WHERE id_branch = '".$_SESSION['id_a_b']."' 
                    AND date_create LIKE '$date%'
                    AND type = 3";
                    
            $db->setQuery($sql);
            return $db->loadObjectList();            
        }   
        
	    //nhan don hang co phat sinh
        function nhan_hang_phat_sinh()
        {
            $id = $_REQUEST['id_order'];
            $db = &JFactory::getDBO();
            $sql = "SELECT wh.id_product AS id
                FROM #__erp_agent_branch_warehouse_order ot, #__erp_agent_branch_warehouse wh
                WHERE ot.id = '".$id."'
                AND ot.id_ab_whouse = wh.id";
            $db->setQuery($sql);
            $rs = $db->loadObject();  
            
            $sql = "SELECT * from #__erp_agent_branch_warehouse where
            id_a_b = '".$_SESSION['id_a_b']."' 
            AND id_product = '".$rs->id."'";
            $db->setQuery($sql);
            $wh = $db->loadObject();
            //print_r($rs);die;
            if( count($wh)==1)
            {
                $sql = "UPDATE #__erp_agent_branch_warehouse_order SET
                                status = 3
                                WHERE 
                                id ='".$id."'";
                                $db->setQuery($sql);
                                $db->query();   
                
                $num = $wh->number_of;
                $num = $wh->number_of + $_POST['number_of'];
                $sql = "UPDATE #__erp_agent_branch_warehouse SET 
                                number_of = '".$num."'
                                WHERE 
                                id_a_b = '".$_SESSION['id_a_b']."' 
                                AND id_product = '".$rs->id."'";
                                $db->setQuery($sql);
                                $db->query();         
            }
            else
            {
                $row =  & JTable::getInstance('were_house', 'Table');  
                $row->id_a_b = $_SESSION['id_a_b'];
                $row->id_product= $rs->id;
                $row->number_of = $_POST['number_of'];
                if (!$row->store()) 
                { //thuc thi them record  neu co loi se hien thi
                              	     JError::raiseError(500, $row->getError() );  
                }
                $sql = "UPDATE #__erp_agent_branch_warehouse_order SET
                                status = 3
                                WHERE 
                                id ='".$id."'";
                                $db->setQuery($sql);
                                $db->query();   
            }
            
            $row =  & JTable::getInstance('phat_sinh', 'Table');
            $row->number_of = $_POST['number_of'];
            $row->note = $_POST['note'];
            $row->id_order = $_POST['id_order'];
            if (!$row->store()) 
                { //thuc thi them record  neu co loi se hien thi
                              	     JError::raiseError(500, $row->getError() );  
                }
            //print_r($row);die;
        }
           
        //nhan hang tu chi nhanh tren goi
        function nhan_hang()
        {
            
            $id = $_REQUEST['id'];
            $db = &JFactory::getDBO();
            $sql = "SELECT wh.id_product AS id, ot.number_of AS num
                FROM #__erp_agent_branch_warehouse_order ot, #__erp_agent_branch_warehouse wh
                WHERE ot.id = '".$id."'
                AND ot.id_ab_whouse = wh.id";
            $db->setQuery($sql);
            $rs = $db->loadObject();  
            $sql = "SELECT * from #__erp_agent_branch_warehouse where
            id_a_b = '".$_SESSION['id_a_b']."' 
            AND id_product = '".$rs->id."'";
            $db->setQuery($sql);
            $wh = $db->loadObject();
            
            if( count($wh)==1)
            {
                $sql = "UPDATE #__erp_agent_branch_warehouse_order SET
                                status = 3
                                WHERE 
                                id ='".$id."'";
                                $db->setQuery($sql);
                                $db->query();   
                
                $num = $wh->number_of;
                $num = $wh->number_of + $rs->num;
                $sql = "UPDATE #__erp_agent_branch_warehouse SET 
                                number_of = '".$num."'
                                WHERE 
                                id_a_b = '".$_SESSION['id_a_b']."' 
                                AND id_product = '".$rs->id."'";
                                $db->setQuery($sql);
                                $db->query();         
            }
            else
            {
                $row =  & JTable::getInstance('were_house', 'Table');  
                $row->id_a_b = $_SESSION['id_a_b'];
                $row->id_product= $rs->id;
                $row->number_of = $rs->num;
                if (!$row->store()) 
                { //thuc thi them record  neu co loi se hien thi
                              	     JError::raiseError(500, $row->getError() );  
                }
                $sql = "UPDATE #__erp_agent_branch_warehouse_order SET
                                status = 3
                                WHERE 
                                id ='".$id."'";
                                $db->setQuery($sql);
                                $db->query();   
            }
            
            
        }
        
	    //chuyen hang xuong chi nhanh, kiot
        function chuyen_hang_xuong($id)
        {
            $db = &JFactory::getDBO();
            //lay thong tin ben table order
            $sql = "SELECT * from #__erp_agent_branch_warehouse_order where id = '".$id."' ";
            $db->setQuery($sql);
            $rs = $db->loadObject();  
            
            //cap nhat lai so luong trong kho khi chuyen xuong, giam so luong
            $sql = "select number_of from  #__erp_agent_branch_warehouse where id = '".$rs->id_ab_whouse."'";   
            $db->setQuery($sql);
            $num = $db->loadResult(); 
            $num = $num - $rs->number_of;//echo $num;die;
            
            $sql = "UPDATE #__erp_agent_branch_warehouse SET 
                                number_of = '".$num."'
                                WHERE 
                                id = '".$rs->id_ab_whouse."'";
                                $db->setQuery($sql);
                                $db->query(); 
                                
            $sql = "UPDATE #__erp_agent_branch_warehouse_order SET
                                status = 2
                                WHERE 
                                id ='".$id."'";
                                $db->setQuery($sql);
                                $db->query();   
        }
        
	    //xac nhan don hang
        function xac_nhan_order($id)
        {
            $db = &JFactory::getDBO();
            $sql = "UPDATE #__erp_agent_branch_warehouse_order SET
                                status = 1
                                WHERE 
                                id ='".$id."'";
                                $db->setQuery($sql);
                                $db->query();   
        }      
        
        //lay danh sach don hang theo id_a_b
        function ds_orther_id_a_b($date=null)
        {
            if($date == null)
            {
                $date = date('Y-m-d');
            }
            //dinh dang lai $date giong voi CSDL
            else
            {
                $d = substr($date,3,2);
                $m = substr($date,0,2);
                $y = substr($date,6,4);
                $date = $y."-".$m."-".$d;
                
            }  
             $db = &JFactory::getDBO();
             $sql = "SELECT * from #__erp_agent_branch_warehouse_order where id_ab_order = '".$_SESSION['id_a_b']."' AND date LIKE '$date%' ";
             
             $db->setQuery($sql);
             return $db->loadObjectList();         
        }
        
        
        //su luoc nhan don dat hang
        function su_luoc_nhan_don_hang($date=null)
        {
            if($date == null)
            {
                $date = date('Y-m-d');
            }
            //dinh dang lai $date giong voi CSDL
            else
            {
                $d = substr($date,3,2);
                $m = substr($date,0,2);
                $y = substr($date,6,4);
                $date = $y."-".$m."-".$d;
                
            }  
            
            $db = &JFactory::getDBO();
            $sql = "SELECT ep.name, ot.number_of, ot.date, ot.status, ot.id as ot_id, ot.id_ab_whouse as id_wh, br.name as ten_chi_nhanh_dat_hang
                    FROM #__erp_product ep, #__erp_agent_branch_warehouse wh, #__erp_agent_branch_warehouse_order ot, #__erp_agent_branch br
                    WHERE ep.id_product = wh.id_product
                    AND wh.id = ot.id_ab_whouse
                    AND br.brand_parent = '".$_SESSION['id_a_b']."'
                    AND ot.date LIKE '$date%'
                    ORDER BY br.name,ot.status
                    ";
                    
            $db->setQuery($sql);
            return $db->loadObjectList();    
        }
        
            	       
        //kiem tra cha con kq tra ve = 0 thi cap thap nhat. 1 thi cap trung gian. 2 thi cap cao nha	   
        function kiem_tra()
        {
            $user = JFactory::getUser();
            $db = &JFactory::getDBO();
            $sql = "select id_a_b from #__erp_agent_branch_user where id_user ='".$user->get('id')."' ";
       	    $db->setQuery($sql);
        	$id_a_b = $db->loadResult();
            $_SESSION['id_a_b'] = $id_a_b;
            $sql = "select count(*) from #__erp_agent_branch where 
            brand_parent ='".$id_a_b."' ";
       	    $db->setQuery($sql);
        	$kq = $db->loadResult();
            //neu bang khong -> cap thap nhat
            if($kq==0)
                return 0;
            else
                return 1;
        }	
        
        //lay id cha
        function lay_id_cha()
        {
            $db = &JFactory::getDBO();
            $sql = "select brand_parent  from #__erp_agent_branch where id ='".$_SESSION['id_a_b']."' ";
       	    $db->setQuery($sql);
        	$id_cha = $db->loadResult();
            $_SESSION['id_cha'] = $id_cha;
            return $id_cha;
        }
        
        //su luoc dat hang
        function su_luoc_dat_hang($date=null)   
		{
		    if($date == null)
            {
                $date = date('Y-m-d');
            }
            //dinh dang lai $date giong voi CSDL
            else
            {
                $d = substr($date,3,2);
                $m = substr($date,0,2);
                $y = substr($date,6,4);
                $date = $y."-".$m."-".$d;
                
            }  
            $db = &JFactory::getDBO();
            $sql = "SELECT ep.name, ot.number_of, ot.date, ot.status, ot.id as ot_id
                    FROM #__erp_product ep, #__erp_agent_branch_warehouse wh, #__erp_agent_branch_warehouse_order ot
                    WHERE ep.id_product = wh.id_product
                    AND wh.id = ot.id_ab_whouse
                    AND ot.id_ab_order = '".$_SESSION['id_cha']."' 
                    AND ot.date LIKE '$date%'";
            $db->setQuery($sql);
            return $db->loadObjectList();
        }
	}
?>